-- vim: set autoindent noexpandtab tabstop=4 shiftwidth=4 softtabstop=-1:
--[[
	Copyright (c) 2018 James Dornan
	
	Permission is hereby granted, free of charge, to any person obtaining a copy
	of this software and associated documentation files (the "Software"), to deal
	in the Software without restriction, including without limitation the rights
	to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
	copies of the Software, and to permit persons to whom the Software is
	furnished to do so, subject to the following conditions:
	
	The above copyright notice and this permission notice shall be included in all
	copies or substantial portions of the Software.
	
	THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
	IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
	FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
	AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
	LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
	OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
	SOFTWARE.
--]]

--- postgresql.lua
-- Allows access to a postgresql table as a Lua table. When used in the context
-- of a mintest mod the values are serialized, making tables a legal value,
-- and compressed if over  the 50 bytes in size. Not all methods are supported
-- by luajit at this time(3/2/2018), but are included in advance of support.
--
-- @module dbtable.postgresql
-- @author James Dornan <james@catch22.com>
-- @copyright James Dornan <james@catch22.com> 2018
-- @release 1.0

---@usage
local usage = [[
local tbl = require('dbtable').new({
    db         = 'postgresql',
    name       = 'test',
    connection = 'dbname=minetest host=localhost user=<username> password=<password>'
})

tbl[1] = {'Hello', 'World'}
tbl[2] = 'b'
tbl[3] = 'c'
tbl[5] = 'e'

print(tbl[1][1] .. ' ' .. tbl[1][2]) -- 'Hello World'

-- The above example would create a postgresql database, and then create within it
-- a table called 'test' with 2 columns, key and value. Key it set as a primary
-- key in the database, and must be unique.

-- This would be the result.

$psql minetest
minetest=# \d test
Table 'public.test'
Column |          Type          | Modifiers
-------+------------------------+-----------
key    | character varying(54)  | not null
value  | character varying(512) |
Indexes:
     'test_pkey' PRIMARY KEY, btree (key)

minetest=# select * from test;
key | value
----+-------
  1 | return {'Hello', 'World'}
  2 | b
  3 | c
  5 | e
 (4 row)

minetest=#
]]

--- table: dbtable.postgresql class
local postgresql = {}

--- Constructor function for dbtable.postgresql module
--
--  See: <a href=https://keplerproject.github.io/luasql/>luasql</a>,
--  <a href=https://forum.minetest.net/viewtopic.php?t=12471/>Trusted mods</a>,
--  <a href='http://lua-users.org/wiki/MetatableEvents/'>Metatable Events</a>
--  <a href='https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING'>Postgrsql connection string</a>
--
-- @table opts
-- @return Table object that has all the metamethods for acting as a Lua table [table]
function postgresql.new(opts)
	assert(type(opts.name) == 'string')
	assert(type(opts.connection) == 'string')

	--- table: Used as self in the object
	local self        = {}
	--- table: Metatable to enable metamethods to impersonate a lua table
	local metatable   = {}
	--- string: Name of the postgresql table we will use
	local name        = opts.name
	--- string: Postgresql database connection string
	local conninfo    = opts.connection
	--- int: Maximum key size
	local keySize     = 54
	--- int: Maximum value size
	local valueSize   = 512
	--- int: Minimum bytes before compression
	local minCompress = 50
	--- table: List of legal key types
	local legalKeys   = {
		string = true,
		number = true
	}
	--- table: List of legal value types
	local legalValues = {
		string  = true,
		number  = true,
		boolean = true
	}
	--- class: module luasql.postgresql
	local driver      = assert(
		require 'luasql.postgres',
		'Please install luasql. https://keplerproject.github.io/luasql/'
	)
	--- class: postgresql driver
	local env         = assert(driver.postgres())
	--- resource: connection to postgresql database
	local con         = assert(env:connect(conninfo))
	--- resource: cursor used for return data
	local cur

	--- function: serialize
	local serialize
	--- function: deserialize
	local deserialize

	--- function: compress
	local compress
	--- function: decompress
	local decompress

	if opts.serialize and opts.deserialize then
		serialize   = opts.serialize
		deserialize = opts.deserialize
	end

	-- Add table as a legal value type
	if serialize then
		legalValues.table = true
	end

	if opts.compress and opts.decompress then
		compress   = opts.compress
		decompress = opts.decompress
	end

	--- Set autocommit, or fail.
	assert(con:setautocommit(true))

	--- boonlean: True if the postgresql table was created using this instance
	self.created = false

	--- Returns true is the table exists and false if it does not.
	-- @return boolean
	function self:exists()
		local cur = assert(con:execute(string.format([[
				SELECT 1
				FROM information_schema.tables
				WHERE table_schema = 'public' AND table_name = '%s'
			]],
			con:escape(name)
		)))

		if cur == nil then
			return false
		end

		local res = cur:fetch()
		cur:close()
		return res ~= nil
	end

	--- Drop the table from the database
	-- @return void
	function self:drop()
		local cur = assert(con:execute(string.format([[
				DROP TABLE %s
			]],
			con:escape(name)
		)))
	end

	--- Locates the next lowest numberic key to use
	-- @return The next available numeric key [number]
	function self:nextKey()
	   local cur = assert(con:execute(string.format(
		   [[
			   SELECT  key::int + 1 AS nextkey
			   FROM	%s outside
			   WHERE   outside.key ~ '^\d+$' AND abs(outside.key::int) > 0
				   AND NOT EXISTS
				   (
					   SELECT NULL
					   FROM   %s inside
					   WHERE  (inside.key ~ '^\d+$' AND outside.key ~ '^\d+$')
					   AND
					   (abs(inside.key::int) > 0 AND inside.key::int = outside.key::int + 1)
				   )
			   ORDER BY key ASC LIMIT 1
		   ]],
		   con:escape(name),
		   con:escape(name)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row == nil then
			return 1
		end

		return row.nextkey
	end

	--- The number of rows in the postgresql table we're using
	-- @return number of rows [number]
	function self:len()
		local cur = assert(con:execute(string.format(
			[[SELECT count(1) AS count FROM %s]],
			con:escape(name)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')
		cur:close()

		return row.count
	end

	--- Get a value from the table using a key
	-- @param key, either string or number
	-- @return string value from the table
	function self:get(key)
		assert(legalKeys[type(key)], 'Only number or string key.')

		local cur = assert(con:execute(string.format(
			[[SELECT value FROM %s WHERE key = '%s']],
			con:escape(name),
			con:escape(key)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row == nil then
			return
		end

		local value = row.value

		if decompress and string.sub(value, 1, 8) ~= 'return {' then
			 value = decompress(value)
		end

		if deserialize and string.sub(value, 1, 8) == 'return {' then
			 value = deserialize(value)
		end

		return value
	end

	--- Get the next row after this key in order. Useful for iteration.
	-- @param[opt] key string or number, before the desired row
	-- @return key
	-- @return value
	function self:next(key)
		assert(legalKeys[type(key)] or key == nil, 'Only number, string, or nil.')

		local cur

		if key == nil then
			cur = assert(con:execute(string.format(
				[[SELECT key, value FROM %s WHERE key > '%s' ORDER BY key LIMIT 1]],
				con:escape(name),
				con:escape(key)
			)))
		else
			cur = assert(con:execute(string.format(
				[[SELECT key, value FROM %s ORDER BY key LIMIT 1]],
				con:escape(name)
			)))
		end

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')

		cur:close()

		if row == nil then
			return
		end

		local value = row.value
		local key   = row.key

		if decompress and string.sub(value, 1, 8) ~= 'return {' then
			 value = decompress(value)
		end

		if deserialize and string.sub(value, 1, 8) == 'return {' then
			 value = deserialize(value)
		end

		return key, value
	end

	--- Get the smallest key value in the table
	-- @return the lowest key in the table
	function self:minKey()
		local cur = assert(con:execute(string.format(
			[[SELECT min(key) AS key FROM %s']],
			con:escape(name)
		)))

		if cur == nil then
			return
		end

		local row = cur:fetch({}, 'a')
		cur:close()

		if row ~= nil then
			return row.key
		end
	end

	--- Create table in postgresql based on the name, keysize and valuesize
	-- @return boolean based on success
	function self:create()
		if type(opts.keysize) == 'number' then
			keySize = opts.keysize
		end

		assert((keySize > 0 and keySize < 512), 'Key size out of range.')

		if type(opts.valuesize) == 'number' then
			valueSize = opts.valuesize
		end

		assert((valueSize > 0 and valueSize < 4097), 'Value size out of range.')

		local res = assert(con:execute(string.format([[
			CREATE TABLE IF NOT EXISTS %s
			(
				key   VARCHAR(%u) PRIMARY KEY,
				value VARCHAR(%u)
			)
		]], con:escape(name), keySize, valueSize)))

		return type(res) == 'number'
	end

	--- Set new key value pair in the table
	-- @param key, string or number
	-- @param[opt] value If omitted the row is deleted
	-- @return value returned from the luasql execite fuction
	function self:set(key, value)
		assert(legalKeys[type(key)], 'Only number or string.')

		if value == nil then
			return self:del(key)
		end

		assert(legalValues[type(value)], 'Only number, string, or table.')

		if serialize and type(value) == 'table' then
			value = serialize(value)
		end

		if compress and string.len(value) > minCompress then
			 value = compress(value)
		end

		local res = assert(con:execute(string.format([[
				INSERT INTO %s (key, value) VALUES ('%s', '%s')
				ON CONFLICT (key) DO UPDATE
				SET value = excluded.value
			]],
			con:escape(name),
			con:escape(key),
			con:escape(value)
		)))

		return res
	end

	--- Delete row from table
	-- @usage spawn.Tenplus1 = nil or spawn['Tenplus1'] = nil
	-- @param key The unique key to be deleted
	-- @return value returned from the luasql execite fuction
	function self:del(key)
		return assert(con:execute(string.format([[
			DELETE FROM  %s WHERE key = '%s'
		]], name, key)))
	end

	--- Create copy of table as a lua table
	-- @return table form of the data
	function self.export()
		local cur = assert(con:execute(string.format([[
			SELECT key, value FROM %s ORDER BY key ASC;
		]], name)))

		local data = {}
		local row = cur:fetch ({}, 'a')

		while row do
			data[row.key] = row.value
			row = cur:fetch ({}, 'a')
		end

		return data
	end

	--- Import lua table
	-- @return void
	function self:import(data)
		for key, value in pairs(data) do
			self:set(key, value)
		end
	end

	--- Lookup row from postgresql table
	-- @usage print(spawn.Tenplus1.x) -- 2498
	-- @param key Key to look up in the table
	-- @return mixed, string , int, or table
	function metatable:__index(key)
		return self:get(key)
	end

	--- Add or up a row in the postgresql table
	-- @usage spawn.Tenplus1 = {15, 3, 1500} or spawn['Tenplus1'] = {15, 3, 1500}
	-- @param key The key to look up in the table
	-- @param value The new value to be added to the table
	-- @return boolean
	function metatable:__newindex(key, value)
		return self:set(key, value)
	end

	--- Append numerically
	-- @usage tbl(value) same as tbl[#tbl +] = value
	-- @param value Value to be added to the next numerical key in order
	function metatable:__call(value)
		return self:set(self:nextKey(), value)
	end

	--- Next key/value test from the given key
	-- @usage  key, value = next(sqltbl, key)
	-- @param key Key before the row we want to return
	-- @return key, value
	function metatable:__next(key)
		return self:next(key)
	end

	--- Used by the pairs function to loop through the table
	-- @usage for key, value in ipairs(tbl) do print(key .. ': ' .. value)) end
	-- @return function, table, nil
	function metatable:__pairs()
		local cur = assert(con:execute(string.format([[
			SELECT key, value FROM %s ORDER BY key ASC;
		]], name)))

		local pairsNext = function(table, key)
			if cur == nil then
				return
			end

			row = cur:fetch({}, 'a')

			if row ~= nil then
				return row.key, row.value
			end

			cur:close()
		end
		return pairsNext, self, nil
	end

	--- Used by the ipairs function to loop through the table
	-- @usage for key, value in ipairs(tbl) do print(key .. ': ' .. value)) end
	-- @return function, table, 0
	function metatable:__ipairs()
		local pairsNext = function(table, key)
			local value
			key = key + 1

			value = self:get(key)

			if value then
				return key, value
			end
		end
		return pairsNext, self, 0
	end

	--- For the # table length method
	-- @usage print(#tbl) -- May not work with luajit
	-- @return int Number of rows in the table
	function metatable:__len()
		return self:len()
	end

	--- String context
	-- @return string
	-- @todo Not sure what to do here at the moment
	function metatable:__tostring()
		return name
	end

	--- Garbage collection, gets called with there are no more references to the
	--  object.
	function metatable:__gc()
		if cur then
			pcall(cur:close())
		end
		pcall(con:close())
		pcall(env:close())
	end

	metatable.__metatable = {}

	setmetatable(self, metatable)

	if self:exists() then
		return self
	end

	assert(self:create(), 'Unable to create database table.')
	self.created = true

	return self
end

return postgresql
